<?php
namespace Home\Controller;

class AttendanceController extends CommonController {
     
//     public function index(){
//         if (isset($_SESSION['username'])){
//             $this->display();
//         }else{
//             header("location: ". __ROOT__."/index.php/login");
//         }
//     }
    
 public function find(){

        
        $attendance = D('attendance'); 
        $staff = D('staff');
        
        $uid = $_SESSION['id'];
        $arr = $staff->field('sid')->where("uid=$uid")->find();
        $sid = $arr['sid'];
        
        $sql = "SELECT case when c.flag=0 then '未审核' when c.flag=1 then '批准' when c.flag=2 then '拒绝' end  as flag,
                a.sid,b.name,b.ename,e.name as department,sum(a.type = 1) as overworknum,sum(a.type = 2) as latenum,sum(a.type = 3) as leavenum ,concat(sum(c.flag=0),'/',count(c.flag)) as flagper 
            FROM (`tbl_attendance` as a   left JOIN tbl_staff as b on a.sid = b.sid) left join tbl_attendinfo as c on 
            a.iid = c.id left join tbl_attendsign as d on a.lid = d.id left join tbl_dept as e on b.department = e.id  group by a.sid having a.sid = $sid";
        //$list = $attendance->field('tbl_staff.state,tbl_attendance.sid,tbl_staff.name,tbl_staff.ename,tbl_staff.department,sum(tbl_attendance.type = 5) as overworknum,sum(tbl_attendance.type = 2) as latenum,sum(tbl_attendance.type = 1) as leavenum')
        //->join('tbl_staff on tbl_attendance.id = tbl_staff.sid','left')->group('tbl_attendance.type and tbl_attendance.sid')->having('sid='.$sid)->find();
        $list = $attendance->query($sql);
        $data['data']=$list;
        $this->ajaxReturn($data); 
    }

    //个人考勤数据展示
    public function attendinfo(){
    
        $limit=$_GET['limit'];
        $start=$_GET['start'];
        $pagesize=$_GET['page'];
   
        $attendinfo = M(); 
        $staff = D('staff');
        
        $uid = $_SESSION['id'];
        $arr = $staff->field('sid')->where("uid=$uid")->find();
        $sid = $arr['sid'];
         
       $sql = "select a.sid,b.msg,b.explain,d.name,d.ename,e.name as department,case when a.type = 1 then '加班' when a.type = 3 then f.reason when a.type = 2 then '迟到' end as type,
           case when a.type = 2 then FROM_UNIXTIME(c.sworktime,'%Y-%m-%d %H:%i:%s') else FROM_UNIXTIME(b.starttime,'%Y-%m-%d %H:%i:%s') end as ftime,
           case when a.type = 2 then FROM_UNIXTIME(c.eworktime,'%Y-%m-%d %H:%i:%s' ) else FROM_UNIXTIME(b.endtime,'%Y-%m-%d %H:%i:%s' ) end as ltime ,
           case when a.type = 2 then '——' else case when b.flag = 0 then '未审核' when b.flag = 1 then '通过' when b.flag = 2 then '拒绝' end  end  as flag
           from tbl_attendance as a left join tbl_attendinfo as b on b.id = a.iid left join tbl_attendsign as c on a.lid = c.id left join tbl_staff as d on
           a.sid = d.sid left join tbl_dept as e on d.department = e.id left join tbl_leave as f on b.tid = f.id where a.sid = $sid LIMIT $start,$limit";

       $list = $attendinfo->query($sql); 
       
        $countSql = "select count(*) as a from (select a.sid,d.name,d.ename,e.name as department,case when a.type = 1 then '加班' when a.type = 3 then '请假' when a.type = 2 then '迟到' end as type,
           case when a.type = 2 then c.sworktime else b.starttime end as ftime,case when a.type = 2 then c.eworktime else b.endtime end as ltime ,
           case when a.type = 2 then '——' else case when b.flag = 0 then '未审核' when b.flag = 1 then '通过' when b.flag = 2 then '拒绝' end  end  as flag
           from tbl_attendance as a left join tbl_attendinfo as b on b.id = a.iid left join tbl_attendsign as c on a.lid = c.id left join tbl_staff as d on
           a.sid = d.sid left join tbl_dept as e on d.department = e.id where a.sid = $sid) as result";
        $count = $attendinfo->query($countSql);
      
       
        $data['total'] = $count[0]['a'];
        
        $data['data']=$list;
        //var_dump($data);
        $this->ajaxReturn($data);
    }
    
    //员工考勤展示
    public function staff(){
        //if(isset(I('sid'))){
            $limit=$_GET['limit'];
            $start=$_GET['start'];
            $pagesize=$_GET['page'];
             
            $attendinfo = M();
    
    
            $sid = I('sid');
             if($sid){
                $sql = "select a.iid,a.sid,b.msg,b.explain,d.name,d.ename,e.name as department,case when a.type = 1 then '加班' when a.type = 3 then f.reason end as type,
                FROM_UNIXTIME(b.starttime,'%Y-%m-%d %H:%i:%s')  as ftime,
                FROM_UNIXTIME(b.endtime,'%Y-%m-%d %H:%i:%s')  as ltime ,
                case when b.flag = 0 then '未审核' when b.flag = 1 then '通过' when b.flag = 2 then '拒绝' end  as flag
                from tbl_attendance as a left join tbl_attendinfo as b on b.id = a.iid  left join tbl_staff as d on
                a.sid = d.sid left join tbl_dept as e on d.department = e.id left join tbl_leave as f on b.tid = f.id  where a.sid = $sid and a.lid = 0 LIMIT $start,$limit";
            
                $list = $attendinfo->query($sql);
                 
                $countSql = "select count(*) as a from (select a.iid,a.sid,d.name,d.ename,e.name as department,case when a.type = 1 then '加班' when a.type = 3 then '请假' end as type,
                FROM_UNIXTIME(b.starttime,'%Y-%m-%d %H:%i:%s')  as ftime,
                FROM_UNIXTIME(b.endtime,'%Y-%m-%d %H:%i:%s')  as ltime ,
                case when b.flag = 0 then '未审核' when b.flag = 1 then '通过' when b.flag = 2 then '拒绝' end  as flag
                from tbl_attendance as a left join tbl_attendinfo as b on b.id = a.iid  left join tbl_staff as d on
                a.sid = d.sid left join tbl_dept as e on d.department = e.id where a.sid = $sid and a.lid = 0) as result";
                $count = $attendinfo->query($countSql);
            
                 
                $data['total'] = $count[0]['a'];
            
                $data['data']=$list;
                //var_dump($data);
                $this->ajaxReturn($data);
             }
     //   }
    }
    
    //部门信息列表数据展示
    public function select(){
        
        $limit=$_GET['limit'];
        $start=$_GET['start'];
        $pagesize=$_GET['page'];
        
        $attendance = M();
       
   
        //字段flagper 指 未审核个数占比所有申请的个数
        $sql = "SELECT  case  when c.flag=0 then '未审核' when c.flag=1 then '批准' when c.flag=2 then '拒绝' end  as flag,a.sid,b.name,b.ename,e.name as department,sum(a.type = 1) as overworknum,sum(a.type = 2) as latenum,
        sum(a.type = 3) as leavenum ,sum(c.type = 1 and c.flag = 0) as uncheckedwork,sum(c.type = 3 and c.flag = 0) as uncheckedleave,sum(c.type = 1 and c.flag = 1) as okcheckedwork,sum(c.type = 3 and c.flag = 1) as okcheckedleave,
        sum(c.type = 1 and c.flag = 2) as failcheckedwork,sum(c.type = 3 and c.flag = 2) as failcheckedleave,concat(sum(c.flag=0),'/',count(c.flag))   as flagper 
            FROM (tbl_attendance as a   left JOIN tbl_staff as b on a.sid = b.sid) left join tbl_attendinfo as c on a.iid = c.id left join tbl_attendsign as d on a.lid = d.id 
            left join tbl_dept as e on b.department = e.id group by a.sid LIMIT $start,$limit ";
        $list = $attendance->query($sql);
        //$list['flagmsg'] = $list['allunchecked'].'/'.$list['allflag'];
        $countSql = "select count(*) from (SELECT c.flag,a.sid,b.name,b.ename,b.department,sum(a.type = 1) as overworknum,sum(a.type = 2) as latenum,sum(a.type = 3) as leavenum 
            FROM (tbl_attendance as a   left JOIN tbl_staff as b on a.sid = b.sid) left join tbl_attendinfo as c on a.iid = c.id left join tbl_attendsign as d on a.lid = d.id group by a.sid LIMIT $start,$limit) as result";
        $count = $attendance->query($countSql);
        
        $data['total'] = $count;
        $data['data']=$list;
        $this->ajaxReturn($data);       
    }
    
    //员工申请加班或者请假 根据type来判断
    public function apply(){        
        
     
        $attendinfo = D('attendinfo');

        $data['starttime'] = I('starttime');
        $data['endtime'] = I('endtime');
       // $data['num'] = I('num');
        $data['starttime'] = strtotime($data['starttime']);
        $data['endtime'] = strtotime($data['endtime']);
        $data['type'] = I('type');
        $data['tid'] = 0;
        $data['tid'] = I('tid');
        $data['explain'] = I('explain');
        $data['flag'] = 0;
        
        if (!$attendinfo->create()){
            
            // 如果创建失败 表示验证没有通过 输出错误提示信息
            $result['success'] = false;
            $result['msg'] = $attendinfo->getError();
            exit($this->ajaxReturn($result));
        }else{            
                if($iid = $attendinfo->add($data)){
                    
                    $staff = D('staff');
                    
                    $uid = $_SESSION['id'];
                    $arr = $staff->field('sid')->where("uid=$uid")->find();
                    $sid = $arr['sid'];
                    
                   
                    $lid = 0;
                    $type = $data['type'];
              
               M()->procedure(" call insertattendance($sid,$iid,$lid,$type);" );
               // var_dump($a);die;
    			$result['success']=true;
    			$result['msg']='申请已提交,请耐心等待审核';
    		}else{
    			$result['success']=false;
    			$result['msg']='申请失败';
    		}
    		echo $this->ajaxReturn($result);
        }       
    }
                  
    //下载员工每日签到表模板
    public function downloadExcel(){
        //Excel表格式,这里简略写了8列
        $letter = array('A','B','C','D');
        //表头数组
        $tableheader = array('工号','上班时间','下班时间','日期');
        //表格数组
        $data = array(
            array(135,'9:11','19:25','2016-4-14'),
            array(136,'8:51','17:25','2016-4-14'),
            array(137,'8:11','19:25','2016-4-14'),
            array(1,'9:11','17:25','2016-4-14'),
        );
        downloadExc($letter,$tableheader,$data);
    }
    
    //上传员工每日签到表
    public function signUpload(){
      
        $attendancesign = D('attendsign');     
        $attendance = D('attendance');
           $path = I('path');
           header("Content-type: text/html; charset=utf-8");
           import('Vendor.PHPExcel.PHPExcel','','.php');//导入类库
           import('Vendor.PHPExcel.PHPExcel.IOFactory','','.php');//导入类库
           import('Vendor.PHPExcel.PHPExcel.Reader.Excel2003','','.php');//导入类库
       
       
           $uploadfile='./Public/'.$path;//Excel文件的路径
       
           $objReader = \PHPExcel_IOFactory::createReader('Excel5');/*Excel5 for 2003 excel2007 for 2007*/
           // $objReader = \PHPExcel_IOFactory::createReaderForFile($filename);
       
           $objPHPExcel = $objReader->load($uploadfile); //读取Excel表格的信息
       
           /*        $sheet = $objPHPExcel->getSheet(0);//获得sheet1
            //var_dump($sheet);exit();
            $highestRow = $sheet->getHighestRow(); // 取得总行数
           $highestColumn = $sheet->getHighestColumn(); // 取得总列数 */
       
       
           //$objPHPExcel = new \PHPExcel();//类库没有用命名空间时 加 \  否则会调用当前命名空间
       
           $objWorksheet = $objPHPExcel->getActiveSheet();
           $highestRow = $objWorksheet->getHighestRow();   // 取得总行数
           $highestColumn = $objWorksheet->getHighestColumn();
           $highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);//总列数
       
           for ($row = 2;$row <= $highestRow;$row++)         {
               $strs=array();
               //注意highestColumnIndex的列数索引从0开始
               for ($col = 0;$col < $highestColumnIndex;$col++)            {
                   $strs[$col] =$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
               }             
                $sworktime = $strs[1];
                $eworktime = $strs[2];
                $date = $strs[3];
               $data['sworktime'] = strtotime($strs[3].' '.$sworktime);
               $data['eworktime'] = strtotime($strs[3].' '.$eworktime);             
               $data['sid'] = $strs[0];
               $data['date'] = strtotime($date);
       
               $adata['sid'] = $strs[0];
               $adata['iid'] = 0;
               $adata['type'] = 2;
             
               $attendancesign->startTrans();
               if($adata['lid'] = $attendancesign->add($data)){
                   if(($sworktime<='9:00') && ($eworktime>='18:00')){                 
                       $attendancesign->commit();
                       $result['success']=true;
                       $result['msg']='保存成功';       
                   }else{
                      
                       if($attendance->add($adata)){
                           $attendancesign->commit();
                           $result['success']=true;
                           $result['msg']='保存成功';
                       }else{
                           $attendancesign->commit();
                           $result['success']=true;
                           $result['msg']='保存成功';
                       }                      
                   }
               }
           }
           $this->ajaxReturn($result);
    }   
    
    //删除选中的数据
    public function delete(){
        $data=I('data');
        $staff = D('dept');
        $map['id']=array('in',$data);
        //echo $post;
        if($staff->where($map)->delete()>0){
            $result['success']=true;
			$result['msg']='删除成功';
        }else{
            $result['success']=false;
			$result['msg']='删除失败';
        }

        $this->ajaxReturn($result);
    }

    //请假类型数据源
    public function leave(){
        $leave = D('leave');
        $list = $leave->select();            
         
       
        $this->ajaxReturn($list);
    }
    //设置员工部门
    public function checkApl(){
        $attendinfo = D('attendinfo');
        //$data['department'] = I('post.department');
        $id = I('iid');
        $data['flag'] = I('flag');
        $data['msg'] = I('msg');
        /*     if(!$data['id']){
         $uid = $_SESSION['id'];
         $arr = $dept->field('id')->where("uid=$uid")->find();
         $data['id'] = $arr['id'];
        }  */
         
         
        if($attendinfo->where("id=".$id)->save($data)!==false){
            //$result['data'] = $data;
            $result['success']=true;//返回修改后的状态
            $result['msg']='修改成功';//状态信息
        }else{
            $result['success']=false;
            $result['msg']='修改失败';
        }
        $this->ajaxReturn($result);
    }
}